/*
 * TransactionLineDAO.java
 *
 * Created on March 5, 2007, 4:19 PM
 *
 */

package edu.byu.isys413.jjenkins.data;

import java.sql.*;
import java.util.*;

 /**
 * This method is used to implement the CRUD methods of a transactionLine. In either
 * to create an object or interact with the database. This method must be called.
 * 
 * @author      Jeff Jenkins
 * @version     1.0
 * 
 */
public class TransactionLineDAO {
    
    /**
     * singleton variable
     */
    private static TransactionLineDAO instance = null;
    
    /** Creates a new instance of TransactionLineDAO */
    private TransactionLineDAO() {
    }
    
    /** This method implements the singleton pattern. It insures that only
     * on instance of TransactionLineDAO exists
     *
     * @return instance */
    

    public static synchronized TransactionLineDAO getInstance() {
        if (instance == null) {
            instance = new TransactionLineDAO();
        }
        return instance;
    }
    
 
    
    /**
     * This method creates a new TransactionLineBO, creates and sets the GUID, and
     * returns it to the calling method
     * @throws edu.byu.isys413.jjenkins.data.DataException formatting
     * @return the BO
     */
    public TransactionLineBO create() throws DataException {
        try {
            String id = GUID.generate();
            TransactionLineBO line = new TransactionLineBO();
            line.setId(id);
            line.setStatus("normal");
            return line;
            
            
        } catch (Exception e) {
            throw new DataException("Could not create GUID", e);
        }
        
    }
    
    
    /**
     * This is the public read statement.  It loads an existing record
     * from the database.
     * @param id id to read
     * @throws edu.byu.isys413.jjenkins.data.DataException format
     * @return BO read
     */
    public synchronized TransactionLineBO read(String id) throws DataException {
        
        // Checks to see if the item is in cache
        Cache cache = Cache.getInstance();
        TransactionLineBO line = (TransactionLineBO)cache.get(id);
        
        try {
            
            // Gets connection
            ConnectionPool connPool = ConnectionPool.getInstance();
            Connection conn = connPool.get();
            
            // If the item is not in cache a connection is checked out and
            // The item is read from the database.
            if(line == null){
                try {
                    //reads in customer
                    line = read(id, conn);
                    
                }catch (SQLException e) {
                    conn.rollback();
                    throw new DataException("Could not retrieve record for id=" + id, e);
                }
            }
            
            // After all items are read the
            // connection committs and is returned.
            conn.commit();
            connPool.release(conn);
            
            
            
        } catch (SQLException e) {
            throw new DataException("Could not retrieve record for id=" + id, e);
        } catch (DataException e) {
            throw new DataException("Could not retrieve record for id=" + id, e);
        } catch (ConnectionPoolException e) {
            throw new DataException("Could not retrieve record for id=" + id, e);
        }
        
        // Return Object
        return line;
    }
    
    /**
     *  This is a package method that is called by the public read (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     * 
     * 
     * @return TransactionLineBO
     * @param id 
     * @param conn 
     * @throws java.sql.SQLException 
     * @throws edu.byu.isys413.jjenkins.data.DataException 
     */
    synchronized TransactionLineBO read(String id, Connection conn) throws SQLException, DataException {
        
        
        // Checks to see if the item is in cache
        Cache cache = Cache.getInstance();
        TransactionLineBO line = (TransactionLineBO)cache.get(id);
        
        // Quieries that database
        if(line == null){
            
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM transaction_line WHERE transaction_line_id = ?");
            pstmt.setString(1, id);
            pstmt.execute();
            ResultSet rs = pstmt.getResultSet();
            
            if (rs.next()) {
                line = new TransactionLineBO();
                line.setId(rs.getString("transaction_line_id"));
                String coupon =rs.getString("coupon");
                if(coupon != null){
                    line.setCoupon(ConceptualCouponDAO.getInstance().read(coupon, conn));
                }
                // This get the revenue source type then it gets the revenue source dao then it reads
                line.setRevenueSource((RevenueSourceFactory.getInstance().getDAO(RevenueSourceDAO.getType(rs.getString("revenue_source"), conn)).read(rs.getString("revenue_source"), conn)));
                line.setTransactionID(rs.getString("transaction_id"));
                line.setStatus(rs.getString("status"));
                
                
                
                // Sets already in database
                line.setObjectAlreadyInDB(true);
                
                // adds to cache
                cache.put(line.getId(), line);
                
            }else{
                throw new DataException("Object was not found in the database.");
            }
        }
        
        
        // returns object
        return line;
    }
    
    
    
    /**
     * This is the public save method.  It is what is called when
     * the user (controller) code wants to save or update an object
     * into the database.
     * r
     * @param line line to save
     * @throws edu.byu.isys413.jjenkins.data.DataException format
     */
    public synchronized void save(TransactionLineBO line) throws DataException {
        try {
            
            
            // retrieve a database connection from the pool
            ConnectionPool connPool = ConnectionPool.getInstance();
            
            Connection conn = connPool.get();
            
            
            try {
                
                // Saves Customer
                save(line, conn);
                
            }catch (SQLException e) {
                conn.rollback();
                throw new DataException("Could not save record for" + line.getId(), e);
            }
            
            // After all items are saved the
            // connection committs and is returned.
            conn.commit();
            connPool.release(conn);
            
            // touch the cache for the object
            Cache.getInstance().touch(line.getId());
            
            
            
            
        } catch (DataException e) {
            throw new DataException("Could not save record for" + line.getId(), e);
        } catch (ConnectionPoolException e) {
            throw new DataException("Could not save record for" + line.getId(), e);
        } catch (SQLException e) {
            throw new DataException("Could not save record for" + line.getId(), e);
        }
        
        
    }
    
    /**
     *  This is a package method that is called by the public save (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     * 
     *  By having other DAOs call this save method (rather than update or
     *  insert below, each DAO in a chained save (like the CustomerDAO calling
     *  MembershipDAO calling InterestDAO save chain) can independently decide
     *  whether to udpate or insert the BO it's saving.  That's why I made
     *  update and insert private rather than package level -- if you call
     *  them directly from another DAO, this DAO can't decide whether it's
     *  object needs to be inserted or updated.
     * @param line 
     * @param conn connection to the databse
     * @throws java.sql.SQLException 
     * @throws edu.byu.isys413.jjenkins.data.DataException 
     */
    synchronized void save(TransactionLineBO line, Connection conn) throws SQLException, DataException {
        
        // check the dirty flag in the object.  if it is dirty,
        // run update or insert
        if (line.isDirty()) {
            if (line.isObjectAlreadyInDB()) {
                update(line, conn);
                
            }else{
                insert(line, conn);
            }
            line.setDirty(false);
        }
        
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL udpate statement and make it more readable.  But
     * logically, it's really just a part of save.
     * @param line 
     * @param conn connection to the databse
     * @throws java.sql.SQLException 
     * @throws edu.byu.isys413.jjenkins.data.DataException 
     */
    private synchronized void update(TransactionLineBO line, Connection conn) throws SQLException, DataException {
        
        PreparedStatement pstmt = conn.prepareStatement("UPDATE transaction_line SET transaction_line_id = ?, coupon = ?, revenue_source = ?, transaction_id = ?, status = ? WHERE transaction_line_id = ?");
        pstmt.setString(1, line.getId());
        pstmt.setString(2, line.getCouponID());
        pstmt.setString(3, line.getRevenueSourceID()); // this is bad coupling, but I couldn't figure how else to do it
        pstmt.setString(4, line.getTransactionID()); // this is bad coupling, but I couldn't figure how else to do it
        pstmt.setString(5, line.getStatus()); // this is bad coupling, but I couldn't figure how else to do it
        pstmt.setString(6, line.getId());
        pstmt.execute();
        
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL insert statement and make it more readable.  But
     * logically, it's really just a part of save.
     * 
     * 
     * @param line 
     * @param conn 
     * @throws java.sql.SQLException 
     * @throws edu.byu.isys413.jjenkins.data.DataException 
     */
    private synchronized void insert(TransactionLineBO line, Connection conn) throws SQLException, DataException {
        
        // Creates prepared statemetn
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO transaction_line VALUES(?, ?, ?, ?, ?)");
        pstmt.setString(1, line.getId());
        pstmt.setString(2, line.getCouponID());
        pstmt.setString(3, line.getRevenueSourceID()); // this is bad coupling, but I couldn't figure how else to do it
        pstmt.setString(4, line.getTransactionID()); // this is bad coupling, but I couldn't figure how else to do it
        pstmt.setString(5, line.getStatus());
        pstmt.execute();
        
        //Sets already in database true
        line.setObjectAlreadyInDB(true);
        
    }
    
    
    /**
     * Transactions Lines are only allowed to be deleted from a transaction
     * only before they are sold on the transaction. The transaction BO has a method
     * to delete transactions from itself. This can only be done before the transaction
     * is posted. Once the transaction is posted the transaction line is written to the
     * database and it's there for good. The clerk may void the order
     * @param line Bo to delete
     * @throws edu.byu.isys413.jjenkins.data.DataException format
     */
    public synchronized void delete(TransactionLineBO line)throws DataException{
        
        throw new DataException("You can not delete a transaction line once it is saved to the database. You must void the transaction");
    }
    
    
    /**
     * This method allows the calling method to pass in an entire list of transactions lines
     * for a transaction and save them all
     * 
     * @param lines 
     * @param conn 
     * @throws java.sql.SQLException 
     * @throws edu.byu.isys413.jjenkins.data.DataException 
     */
    
    synchronized void saveAllTransactionLines(List lines, Connection conn) throws SQLException, DataException {
        
        for(int i = 0; i < lines.size(); i++) {
            
            TransactionLineBO line = (TransactionLineBO)lines.get(i);
            
            // check the dirty flag in the object.  if it is dirty,
            // run update or insert
            if (line.isDirty()) {
                if (line.isObjectAlreadyInDB()) {
                    update(line, conn);
                    
                }else{
                    insert(line, conn);
                }
                line.setDirty(false);
            }
            
        }
        
    }
    
    /**
     * read all of the tlines associated with specific transaction
     * @param transid trans to read
     * @throws java.sql.SQLException sql errors
     * @throws edu.byu.isys413.jjenkins.data.DataException format
     * @throws edu.byu.isys413.jjenkins.data.ConnectionPoolException conn pool
     * @return list of all the tlines on the transaction
     */
    public List readByTransaction(String transid) throws SQLException, DataException, ConnectionPoolException{
        List transLines = new LinkedList();
        Connection conn = ConnectionPool.getInstance().get();
        transLines = readByTransaction(transid, conn);
        return transLines;
    }
    
    
    /**
     * reads tlines by transaction
     * @param transid trans to read
     * @param conn connection
     * @throws java.sql.SQLException sql errors
     * @throws edu.byu.isys413.jjenkins.data.DataException format
     * @return list of tlines
     */
    public synchronized List readByTransaction(String transid, Connection conn) throws SQLException, DataException {
        
        List transLines = new LinkedList();
        
        // prepares statemnt
        
        PreparedStatement pstmt = conn.prepareStatement("SELECT transaction_line_id FROM transaction_line WHERE transaction_id = ?");
        pstmt.setString(1, transid);
        pstmt.execute();
        ResultSet rs = pstmt.getResultSet();
        
        
        while(rs.next()){
            String line_id = rs.getString("transaction_line_id");
            //get the line id, then use the already made read to get the rest of the information...very cool
            TransactionLineBO line = read(line_id, conn);
            //line.setTransaction(transaction);//i took out this line because i do not need it for my method -rick
            transLines.add(line);
        }
        
        return transLines;
    }
    
    /**
     *  This method reads all transactions lines for a transaction.
     * @param transaction 
     * @param conn conection to the database
     * @throws java.sql.SQLException 
     * @throws edu.byu.isys413.jjenkins.data.DataException 
     * @return 
     */
    synchronized List readByTransaction(TransactionBO transaction, Connection conn) throws SQLException, DataException {
        
        List transLines = new LinkedList();
        
        // prepares statemnt
        
        PreparedStatement pstmt = conn.prepareStatement("SELECT transaction_line_id FROM transaction_line WHERE transaction_id = ?");
        pstmt.setString(1, transaction.getId());
        pstmt.execute();
        ResultSet rs = pstmt.getResultSet();
        
        
        while(rs.next()){
            String line_id = rs.getString("transaction_line_id");
            TransactionLineBO line = read(line_id, conn);
            line.setTransaction(transaction);
            transLines.add(line);
        }
        
        return transLines;
    }
    
    /**
     * read all tlines that have rs in them
     * @param revSourceID rs to search for
     * @throws edu.byu.isys413.jjenkins.data.DataException format
     * @return list of tlines
     */
    public TransactionLineBO readByRevenueSourceID(String revSourceID) throws DataException{
        TransactionLineBO object = new TransactionLineBO();
        
        try {
            
            // Gets connection
            ConnectionPool connPool = ConnectionPool.getInstance();
            Connection conn = connPool.get();
            
            // Creates statement and populates list
            try {
                
                // prepares statemnt
                PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM transaction_line WHERE revenue_source = ?");
                pstmt.setString(1, revSourceID);
                pstmt.execute();
                ResultSet rs = pstmt.getResultSet();
                
                //get instance of cache to put objects in
                Cache cache = Cache.getInstance();
                
                if(rs.next()){
                    object.setId(rs.getString("transaction_line_id"));
                    object.setCouponID(rs.getString("coupon"));
                    object.setRevenueSourceID(rs.getString("revenue_source"));
                    object.setTransactionID(rs.getString("transaction_id"));
                    
                    cache.put(object.getId(), object);
                    
                }
            }catch (SQLException e) {
                conn.rollback();
                throw new DataException("Could not retrieve list of objects " + e);
            }
            
            // After all items are read (customer, membership, interest) the
            // connection committs and is returned.
            conn.commit();
            connPool.release(conn);
            
            
            
        } catch (SQLException e) {
            throw new DataException("Could not retrieve object " + e);
        } catch (DataException e) {
            throw new DataException("Could not retrieve object " + e);
        } catch (ConnectionPoolException e) {
            throw new DataException("Could not retrieve object " + e);
        }
        
        // Return object
        return object;
    }
    
    
    
    
}
